package com.qs.core.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.service.jdbc.connections.spi.ConnectionProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Transactional(propagation=Propagation.REQUIRED)
@Repository
public class GenericDAOImpl implements GenericDAO
{
	@Autowired
	private SessionFactory sessionFactory;
	protected Log logger;
	
	public GenericDAOImpl()
	{
		logger = LogFactory.getLog(getClass());
	}
	
	public SessionFactory getSessionFactory()throws Exception 
	{
		return sessionFactory;
	}
	
	public void setSessionFactory(SessionFactory sessionFactory) throws Exception
	{
		this.sessionFactory = sessionFactory;
	}

	public Session getSession()throws Exception
	{
		Session session=null;
		try
		{
			session = sessionFactory.getCurrentSession();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			e.printStackTrace(System.out);
			session = SessionFactoryUtils.getNewSession(sessionFactory);
		}
		System.out.println("Session Created------------------- "+ session.hashCode());
		return session;
		
	
	}
	public Long genNextValOfSequence(String seq)throws Exception
	{
		Long seqVal=null;
		Session session= this.getSession();
		String lStrquery="select " + seq + ".nextval from dual";
		Query query = null;
		Iterator itr=null;
		Object[] row=null;
		query=this.getSession().createSQLQuery(lStrquery);
		List list=  query.list();
		itr= (Iterator)list.iterator();
		if(itr.hasNext())
		{
			seqVal=((java.math.BigDecimal)itr.next()).longValue();
		}
	
		return seqVal;
		
	}
	public Session getReadOnlySession()throws Exception
	{
		Session session=this.getSession();
		System.out.println("Session------------------- "+ session.hashCode());
		try 
		 {
			session.setReadOnly(null, true);
		} 
		catch (HibernateException e) 
		{
			System.out.println("Read Write Session-------------------");
			session=this.getSession();
			e.printStackTrace();
		} 
/*		catch (SQLException e) 
		{
			System.out.println("Read Write Session-------------------");
			session=SessionFactoryUtils.openSession(sessionFactory);
			e.printStackTrace();
		}*/
		return session;
	
	} 
	
	public Session getSessionForCaching()throws Exception
	{
		Session session=null;
		try {
		 session= this.getSession();
		 if(session==null)
		 {
			 session=this.getSession();
		 }
		} catch (HibernateException e) 
		{
			session=this.getSession();
		}
		catch (Exception e) 
		{
			session=this.getSession();
		}
		return session;
	}

	public Criteria setProjectionProperties(Criteria objCrt ,String propList[])
	{
		if(propList!=null)
		{
			ProjectionList projList= Projections.projectionList();
			for(int j = 0; j < propList.length; j++)
			{
				projList.add(Projections.property(propList[j]));
			}
			objCrt.setProjection(projList) ;
		}
		return objCrt;
	}

	
	public Serializable create(Object o)throws Exception
	{
		return getSession().save(o);
	}


	public List select(Class type,String propList[])throws Exception
	{
		Criteria objCrt=null;
		objCrt = getSession().createCriteria(type);
		objCrt= this.setProjectionProperties(objCrt, propList);
		return objCrt.list();    
	}


	public Object get(Class type,Serializable id)throws Exception
	{
		return getSession().get(type, id);		
	}

	public void update(Object o)throws Exception
	{
		getSession().update(o); 
	}
	
	public Object saveOrUpdate(Object o)throws Exception
	{
		getSession().saveOrUpdate(o); 
		return o;
	}

	public void updateWithHistory(Object o)
	{
		// Move Existing Data into History Table
	}

	public void delete(Object o)throws Exception
	{
		getSession().delete(o);
	}
 
		public List selectByColumnAndValue(Class type,String aStrColumnName, Object aValueObj, String Orderby,String propList[])throws Exception
	{
		Criteria objCrt=null;
		objCrt = getSession().createCriteria(type);
		this.setProjectionProperties(objCrt, propList);
		if(aStrColumnName!= null)
		{
			objCrt.add(Restrictions.eq(aStrColumnName, aValueObj));
		}
		if(Orderby!= null)
			objCrt.addOrder(Order.asc(Orderby));		
		return objCrt.list();
	}
	
	
	public List selectByColumnAndValueWithIgnoreCase(Class type,String aStrColumnName, Object aValueObj, String Orderby,String propList[])throws Exception
	{
		Criteria objCrt=null;
		objCrt = getSession().createCriteria(type);
		this.setProjectionProperties(objCrt, propList);
		if(aStrColumnName!= null)
		{
			objCrt.add(Restrictions.ilike(aStrColumnName, aValueObj));
		}
		if(Orderby!= null)
			objCrt.addOrder(Order.asc(Orderby));		
		return objCrt.list();
	}

	public List selectByColumnAndValue(Class type,String aStrColumnName[], Object aValueObj[], String orderBy[], String sortingOrder,String propList[])throws Exception
	{
		if((aStrColumnName!=null && aValueObj!= null)&& (aStrColumnName.length != aValueObj.length))
		{
			logger.info("Column Name and Value array length doesn't match");
			return null;
		} 
		Criteria objCrt = getSession().createCriteria(type);
		this.setProjectionProperties(objCrt, propList);
		if(aStrColumnName!=null)
		{
			for(int i = 0; i < aStrColumnName.length; i++)
			{
				String colName = aStrColumnName[i];
				Object colValue = aValueObj[i];           
				objCrt.add(Restrictions.eq(colName, colValue));

			}
		}    
		if(orderBy != null && orderBy.length > 0 && sortingOrder != null && sortingOrder.trim().equalsIgnoreCase("asc"))
		{
			for(int j = 0; j < orderBy.length; j++)
				objCrt.addOrder(Order.asc(orderBy[j]));

		} else
			if(orderBy != null && orderBy.length > 0 && sortingOrder != null && sortingOrder.trim().equalsIgnoreCase("desc"))
			{
				for(int j = 0; j < orderBy.length; j++)
					objCrt.addOrder(Order.desc(orderBy[j]));

			}
		return objCrt.list();
	}

	public List selectByColumnAndValueWithIgnoreCase(Class type,String aStrColumnName[], Object aValueObj[], String orderBy[], String sortingOrder,String propList[])throws Exception
	{
		if((aStrColumnName!=null && aValueObj!= null)&& (aStrColumnName.length != aValueObj.length))
		{
			logger.info("Column Name and Value array length doesn't match");
			return null;
		} 
		Criteria objCrt = getSession().createCriteria(type);
		this.setProjectionProperties(objCrt, propList);
		if(aStrColumnName!=null)
		{
			for(int i = 0; i < aStrColumnName.length; i++)
			{
				String colName = aStrColumnName[i];
				Object colValue = aValueObj[i];           
				objCrt.add(Restrictions.ilike(colName, colValue));

			}
		}    
		if(orderBy != null && orderBy.length > 0 && sortingOrder != null && sortingOrder.trim().equalsIgnoreCase("asc"))
		{
			for(int j = 0; j < orderBy.length; j++)
				objCrt.addOrder(Order.asc(orderBy[j]));

		} else
			if(orderBy != null && orderBy.length > 0 && sortingOrder != null && sortingOrder.trim().equalsIgnoreCase("desc"))
			{
				for(int j = 0; j < orderBy.length; j++)
					objCrt.addOrder(Order.desc(orderBy[j]));

			}
		return objCrt.list();
	}
 

	public List selectForColumnByValues(Class type,String aStrColumnName, List Values,String propList[])throws Exception
	{
		Criteria critObj = getSession().createCriteria(type);
		this.setProjectionProperties(critObj, propList);
		if(!Values.isEmpty())
		{
			critObj.add(Restrictions.in(aStrColumnName, Values));
			return critObj.list();
		}
		return null;
	}

	public Long countRow(Class type)throws Exception
	{
		Criteria objCrt=null;
		List list=null;
		Long row= 0L;
		objCrt = getSession().createCriteria(type);
		objCrt.setProjection(Projections.rowCount());
		list= objCrt.list();
		if(list!=null)
		{
			Iterator itr= (Iterator)list.iterator();
			if(itr.hasNext())
			{
				row =(Long) itr.next();
			}
		}
		return row; 
	}

	public Long countRow(Class type,String aStrColumnName[], Object aValueObj[])throws Exception
	{
		Criteria objCrt=null;
		List list=null;
		Long row= 0L;
		if((aStrColumnName!=null && aValueObj!= null)&& (aStrColumnName.length != aValueObj.length))
		{
			logger.info("Column Name and Value array length doesn't match");
			return -1L;
		} 
		objCrt = getSession().createCriteria(type);
		objCrt.setProjection(Projections.rowCount());
		if(aStrColumnName!=null)
		{
			for(int i = 0; i < aStrColumnName.length; i++)
			{
				String colName = aStrColumnName[i];
				Object colValue = aValueObj[i];
				objCrt.add(Restrictions.eq(colName, colValue));
			}
		}
		list= objCrt.list();
		if(list!=null)
		{
			Iterator itr= (Iterator)list.iterator();
			if(itr.hasNext())
			{
				row =(Long) itr.next();

			}
		}
		return row; 
	}

	public List selectRowsByIndexing(Class type,String aStrColumnName, Object aValueObj, String Orderby, int startIndex, int endIndex) throws Exception
	{
		Criteria objCrt=null;
		List list=null;
		objCrt = getSession().createCriteria(type);
		if(aStrColumnName!= null)
		{
			objCrt.add(Restrictions.eq(aStrColumnName, aValueObj));
		}
		if(Orderby!= null)
			objCrt.addOrder(Order.asc(Orderby));
		objCrt.setFirstResult(startIndex-1);
		objCrt.setMaxResults(endIndex-startIndex+1);
		list= objCrt.list();
		return list;
	}
	
	
	public Connection getConnection()
	{
		SessionFactoryImplementor sessionFactoryImplementor = null;
	    ConnectionProvider connectionProvider = null;
	    java.sql.Connection connection = null ;
	    try 
	    {
			sessionFactoryImplementor = (SessionFactoryImplementor)(getSession().getSessionFactory());
	        connection = sessionFactoryImplementor.getConnectionProvider().getConnection();
	    } 
	    catch (SQLException e)
	    {
	        e.printStackTrace();
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    return connection;
	}

}